/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package EntityDB;

import entity.Department;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author NgocNam
 */
public class departmentDB {
        private String URL = "jdbc:sqlserver://localhost;databaseName=LeaveSystemManager";
        private String Use = "sa"; 
        private String Pass = "123456";
        private Connection conn;
        
        public departmentDB(){
        try {
            conn = DriverManager.getConnection(URL, Use, Pass);
        } catch (SQLException ex) {
            Logger.getLogger(departmentDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        }
        public boolean addDepartment(Department d){
        try {
            CallableStatement csmt=conn.prepareCall("{addDepartment(?,?,?)}");
            csmt.setString(1, d.getDepartmentID());
            csmt.setString(2, d.getDepartmentName());
            csmt.setBoolean(3, d.getStatus());
              int row=csmt.executeUpdate();
              if(row>0){
                  return true;
              }
        } catch (SQLException ex) {
            Logger.getLogger(departmentDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        }
        
        
        public boolean updateDepartment(Department d){
        try {
            CallableStatement csmt=conn.prepareCall("{call updateDepartment(?,?,?)}");
            csmt.setString(1,d.getDepartmentID());
            csmt.setString(2, d.getDepartmentName());
            csmt.setBoolean(3, d.getStatus());
            int row=csmt.executeUpdate();
            if(row>0){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(departmentDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        }
        
        public boolean removeDepartment(String departmentID){
        try {
            CallableStatement csmt=conn.prepareCall("{call removeDepartment(?)}");
            csmt.setString(1,departmentID);
            int row=csmt.executeUpdate();
            if(row>0){
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(departmentDB.class.getName()).log(Level.SEVERE, null, ex);
        }
            return false;
        }
        
        public ArrayList<Department> getDepartment(){
            ArrayList<Department> list=new ArrayList<Department>();
        try {
            CallableStatement csmt=conn.prepareCall("{call getDepartment()}");
            ResultSet rs=csmt.executeQuery();
            while(rs.next()){
                String departmentID=rs.getString(1);
                String departmentName=rs.getString(2);
                boolean status=rs.getBoolean(3);
                    Department d=new Department(departmentID, departmentName, status);
                    list.add(d);
            }
        } catch (SQLException ex) {
            Logger.getLogger(departmentDB.class.getName()).log(Level.SEVERE, null, ex);
        }
        return list;
        }
}
